﻿ALTER PROC UpdateTinhTrang
		@Ma_PD nchar(5)
AS
BEGIN TRAN
	DECLARE @MaMon nchar(5)
	DECLARE @SL int
	DECLARE @cur CURSOR
	SET @cur= CURSOR FOR (SELECT Ma_Mon,SoLuong FROM ChiTietPhieuDat WHERE Ma_PD=@Ma_PD)
	OPEN @CUR
	FETCH NEXT FROM @cur INTO @MaMon,@SL
	WHILE @@fetch_status = 0
	BEGIN
		SELECT @MaMon AS N'Mã món ăn',@SL AS N'Số lượng'
		DECLARE @SoPhan int
		SELECT @SoPhan=SoPhan FROM ThucPhamSoChe WHERE Ma_Mon=@MaMon
		IF(@SoPhan>@SL)
		BEGIN
			DECLARE @SoPhanConLai int
			SET @SoPhanConLai=@SoPhan-@SL
			SELECT  @SoPhanConLai AS N'Sối lượng còn lại'
			WAITFOR DELAY '00:00:05'
			UPDATE ThucPhamSoChe SET SoPhan=@SoPhanConLai WHERE Ma_Mon=@MaMon

		END
		ELSE
		BEGIN

			ROLLBACK TRAN
		END	
		FETCH NEXT FROM @cur INTO @MaMon,@SL	
	END
COMMIT TRAN
EXEC UpdateTinhTrang 255

//Du lieu ban dau
EXEC sp_InsertPhieuDat '255','005',N'Lê Văn Lách Luât',N'9999 Hai Bà Trưng','37477755','2013-05-09 00:00:00.000',236000,1,N'Nhiều rau'
EXEC sp_InsertChiTietPhieuDat'255','255','013',3,27000,135000,null





